/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package export;

import DBConnection.ATeamObject;
import GoodsBean.Goods;
import GoodsBean.GoodsHome;
import categorybean.Category;
import categorybean.CategoryHome;
import java.io.File;
import java.io.FileWriter;
import java.io.StringWriter;
import java.io.Writer;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.ejb.NoSuchEntityException;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.rmi.PortableRemoteObject;
import javax.sql.DataSource;
import javax.xml.XMLConstants;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.TransformerFactoryConfigurationError;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import javax.xml.validation.Schema;
import javax.xml.validation.SchemaFactory;
import javax.xml.validation.Validator;
import manufacturerBean.Manufacturer;
import manufacturerBean.ManufacturerHome;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Text;
import org.xml.sax.SAXException;

/**
 *
 * @author XPYCT
 */
public class XMLExport {
    private DataSource dataSource;
    private Connection connection;
    private DocumentBuilderFactory dbfac;
    private DocumentBuilder docBuilder;
    private Document document;
    
    String getDateQuery = 
            "SELECT TO_DATE(VALUE, 'DD.MM.YYYY HH24:MI:SS') AS VALUE,"
            + " ATTRID FROM PARAMS"
            + " WHERE OBJECTID = ?"
            + " AND ATTRID IN (23,24)"
            + " ORDER BY ATTRID";
    
    String getNameQuery = 
            "SELECT Object.Name"
            + " FROM Object"
            + " WHERE ObjectId = ?"
            + " AND ObjectTypeId = ?";
    
  
    public DOMSource getDocument() {
        return new DOMSource(document);
    } 
    
    public void addShop(Integer shopID, Collection categoriesID,
            Collection manufacturersID, Collection goodsID) throws Exception {
        
        Date date; 
        
        Object homeCategoryObject;
        CategoryHome categoryHome;
        Category category;
        
        Object homeGoodsObject;
        GoodsHome goodsHome;
        Goods goods;

        Object homeManufacturerObject;
        ManufacturerHome manufacturerHome;
        Manufacturer manufacturer;

        ATeamObject ato = new ATeamObject();
        
        PreparedStatement addShopStatement = null;
        ResultSet resultSet = null;
        
        setConnection();
        try {
            dbfac = DocumentBuilderFactory.newInstance();
            docBuilder = dbfac.newDocumentBuilder();
            document = docBuilder.newDocument();
            
            Element shop = document.createElement("shop");
            shop.setAttribute("xmlns:xsi", "http://www.w3.org/2001/XMLSchema-instance");
            shop.setAttribute("xmlns", "http://localhost:8080/ATeamShop-war/");
            //shop.setAttribute("xsi:schemaLocation",
            //        "http://localhost:8080/ATeamShop-war/shop http://localhost:8080/ATeamShop-war/shop.xsd");
            shop.setAttribute("xsi:schemaLocation",
                    "http://localhost:8080/ATeamShop-war/ http://localhost:8080/ATeamShop-war/shop.xsd");
            //shop.setAttribute("id", shopID.toString());
            document.appendChild(shop);

            addShopStatement = connection.prepareStatement(getNameQuery);
            addShopStatement.setInt(1, shopID);
            addShopStatement.setInt(2, 5);
            resultSet = addShopStatement.executeQuery(); 
            if (!resultSet.next()){
                throw new NoSuchEntityException("The shop name isn't found");
            }
            
            Element shopName = document.createElement("name");
            shop.appendChild(shopName);
            Text text = document.createTextNode(resultSet.getString(1));
            resultSet = null;
            shopName.appendChild(text);
            
            addShopStatement = connection.prepareStatement(getDateQuery);
            addShopStatement.setInt(1, shopID);
            resultSet = addShopStatement.executeQuery(); 
            if (!resultSet.next()){
                throw new NoSuchEntityException("The shop dates isn't found");
            }
            Date createDate = resultSet.getDate("Value");
            resultSet.next();
            Date lastModifyDate = resultSet.getDate("Value");
            resultSet = null;
            
            date = createDate;
            
            Element shopCreateDate = document.createElement("createDate");
            shop.appendChild(shopCreateDate);
            text = document.createTextNode(createDate.toString());
            shopCreateDate.appendChild(text);
            
            Element shopLastModifyDate = document.createElement("lastModifyDate");
            shop.appendChild(shopLastModifyDate);
            text = document.createTextNode(lastModifyDate.toString());
            shopLastModifyDate.appendChild(text);
            
            Element shopCatigories = document.createElement("catigories");
            shop.appendChild(shopCatigories);
            
            homeCategoryObject = new InitialContext().lookup("CategoryBean");
            categoryHome = (CategoryHome)PortableRemoteObject.narrow(
                homeCategoryObject,CategoryHome.class);
            //Collection categories = categoryHome.findAll();
            Iterator categoriesIterator = categoriesID.iterator();
           
            while (categoriesIterator.hasNext()){
                //category = (Category)categoriesIterator.next();
                category = categoryHome.findByPrimaryKey(
                        (Integer)categoriesIterator.next());
                
                Element shopCategory = document.createElement("category");
                shopCategory.setAttribute(
                        "id", category.getObjectId().toString());
                shopCatigories.appendChild(shopCategory);
                
                Element categoryName = document.createElement("name");
                shopCategory.appendChild(categoryName);
                text = document.createTextNode(category.getName());
                categoryName.appendChild(text);
                
                Element categoryCreateDate = document.createElement(
                        "createDate");
                shopCategory.appendChild(categoryCreateDate);
                text = document.createTextNode(date.toString());
                categoryCreateDate.appendChild(text);

                Element categoryLastModifyDate = document.createElement(
                        "lastModifyDate");
                shopCategory.appendChild(categoryLastModifyDate);
                text = document.createTextNode(date.toString());
                categoryLastModifyDate.appendChild(text);
                
                Element goodsSequence = document.createElement("goodsSeq");
                shopCategory.appendChild(goodsSequence);
                
                homeGoodsObject = new InitialContext().lookup("GoodsBean");
                goodsHome = (GoodsHome)PortableRemoteObject.narrow(
                    homeGoodsObject,GoodsHome.class);
                ato.setParentId(category.getObjectId());
                
                //Collection allCategoryGoods = goodsHome.findAllByCategoryId(
                //        ato);
                
                Iterator goodsIterator = goodsID.iterator();
           
                while (goodsIterator.hasNext()){
                    //goods = (Goods)goodsIterator.next();
                    goods = goodsHome.findByPrimaryKey(
                            (Integer)goodsIterator.next());
                    
                    if (goods.getCategoryId() == category.getObjectId()){
                        Element categoryGoods = document.createElement("goods");
                        categoryGoods.setAttribute(
                                "id", Integer.toString(goods.getGoodsId()));
                        goodsSequence.appendChild(categoryGoods);

                        Element goodsName = document.createElement("name");
                        categoryGoods.appendChild(goodsName);
                        text = document.createTextNode(goods.getNameOfGoods());
                        goodsName.appendChild(text);

                        Element goodsManufacturerId = document.createElement(
                                "idOfManufacturer");
                        categoryGoods.appendChild(goodsManufacturerId);
                        text = document.createTextNode(
                                Integer.toString(goods.getManufactureId()));
                        goodsManufacturerId.appendChild(text);

                        Element goodsPrice = document.createElement(
                                "price");
                        categoryGoods.appendChild(goodsPrice);
                        text = document.createTextNode(
                                Integer.toString(goods.getPrice()));
                        goodsPrice.appendChild(text);

                        Element goodsNumber = document.createElement(
                                "number");
                        categoryGoods.appendChild(goodsNumber);
                        text = document.createTextNode(
                                Integer.toString(goods.getNumber()));
                        goodsNumber.appendChild(text);

                        Element goodsCreateDate = document.createElement(
                                "createDate");
                        categoryGoods.appendChild(goodsCreateDate);
                        text = document.createTextNode(
                                goods.getGoodsCreateDate().toString());
                        goodsCreateDate.appendChild(text);

                        Element goodsLastModifyDate = document.createElement(
                                "lastModifyDate");
                        categoryGoods.appendChild(goodsLastModifyDate);
                        text = document.createTextNode(
                                goods.getGoodsLastModifyDate().toString());
                        goodsLastModifyDate.appendChild(text);
                    }
                }    
            }
            
            Element shopManufacturers = document.createElement("manufacturers");
            shop.appendChild(shopManufacturers);
            
            homeManufacturerObject = new InitialContext().lookup("ManufacturerBean");
            manufacturerHome = (ManufacturerHome)PortableRemoteObject.narrow(
                homeManufacturerObject,ManufacturerHome.class);
            //Collection manufacturers = manufacturerHome.findAll();
            Iterator manufacturersIterator = manufacturersID.iterator();
           
            while (manufacturersIterator.hasNext()){
                //manufacturer = (Manufacturer)manufacturersIterator.next();
                manufacturer = manufacturerHome.findByPrimaryKey(
                        (Integer)manufacturersIterator.next());
                
                Element shopManufacturer = document.createElement("manufacturer");
                shopManufacturer.setAttribute(
                        "id", manufacturer.getObjectId().toString());
                shopManufacturers.appendChild(shopManufacturer);
                
                Element manufacturerName = document.createElement("name");
                shopManufacturer.appendChild(manufacturerName);
                text = document.createTextNode(manufacturer.getName());
                manufacturerName.appendChild(text);
                
                Element manufacturerCreateDate = document.createElement(
                        "createDate");
                shopManufacturer.appendChild(manufacturerCreateDate);
                text = document.createTextNode(date.toString());
                manufacturerCreateDate.appendChild(text);

                Element manufacturerLastModifyDate = document.createElement(
                        "lastModifyDate");
                shopManufacturer.appendChild(manufacturerLastModifyDate);
                text = document.createTextNode(date.toString());
                manufacturerLastModifyDate.appendChild(text);
            }
            closeConnection();
        } catch (Exception ex) {
            throw new Exception(ex.getMessage());
        }
        
        closeConnection();
    }    
        
    void closeConnection() {
        try {
            if (connection != null){
                connection.close();
                System.out.println("Connection closed");
            }
        } catch (SQLException e) {
            e.getMessage();
        } finally {
            connection = null;
        }
    }
    
    void setConnection(){ 
        try {
            InitialContext initialContext = new InitialContext();
            dataSource = (DataSource) initialContext.lookup(
                        "java:/ATeamConnectionPool");
            System.out.println("dataSource is found");                
        } catch (NamingException e){
            e.getMessage();
        }
        if (connection == null){
            try {
                connection = dataSource.getConnection();
                System.out.println("Connection is ready");
            } catch (SQLException e){
                e.getMessage();
            }
        }    
    }
    
    public void getTextXML() throws Exception{
        try {
            TransformerFactory transfac = TransformerFactory.newInstance();
            Transformer trans = transfac.newTransformer();
            trans.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION, "yes");
            trans.setOutputProperty(OutputKeys.INDENT, "yes");

            StringWriter sw = new StringWriter();
            StreamResult result = new StreamResult(sw);
            DOMSource source = new DOMSource(document);
            trans.transform(source, result);
            System.out.println(sw.toString());
        } catch (Exception ex) {
            throw new Exception (ex.getMessage());
        }
    }
    
    public void validXML() throws Exception{
        try {
            SchemaFactory schemaFactory = SchemaFactory.newInstance(
            XMLConstants.W3C_XML_SCHEMA_NS_URI );
            Schema schemaXSD = schemaFactory.newSchema(
                    new File ("shop.xsd"));
            Validator validator = schemaXSD.newValidator();
            DOMSource ds = new DOMSource(document);
            validator.validate(ds);
        } catch (Exception ex) {
            throw new Exception (ex.getMessage());
        }
    }
}
